package com.roc.web.action.setting;


import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.apache.poi.hssf.util.Region;
import org.hibernate.SQLQuery;
import org.hibernate.Session;

import com.roc.bean.TermBean;
import com.roc.dao.DaoException;
import com.roc.dao.IBaseDao;
import com.roc.dao.ISQLBaseDao;
import com.roc.pojo.Estrnlog;
import com.roc.service.IExcelService;
import com.roc.service.ServiceException;
import com.roc.service.setting.ISettingExcelService;
import com.roc.util.Constance;
import com.roc.util.ExcelUtil;
import com.roc.util.constant.BcTranType;
import com.roc.util.constant.RespCode;
import com.roc.util.constant.ReversalType;

/**
 * 
 * @author zx-think
 *
 */
@Service
@Transactional
@SuppressWarnings("all")
public class SettingExcelServiceImpl implements ISettingExcelService{
	static Logger  logger = Logger.getLogger(SettingExcelServiceImpl.class);
	@Autowired
	IBaseDao  baseDao;
	@Autowired
	ISQLBaseDao sqlBaseDao;
	
	
	/**
	 * @param  结算导出
	 * 
	 * 
	 * */
	@Override
	public HSSFWorkbook settingQueryExcel(SettingSearchCondition searchCondition) throws ServiceException {
			
			HSSFWorkbook wb = new HSSFWorkbook();
			try {
				
				String mid = Constance.Null2EmptyTrim(searchCondition.getMerNo());
				String startDate = searchCondition.getMinDate();
				String endDate = searchCondition.getMaxDate();
				String realName = Constance.Null2EmptyTrim(searchCondition.getRealName());
				String settFlag = Constance.Null2EmptyTrim(searchCondition.getSettFlag());
				String frozenFlag = Constance.Null2EmptyTrim(searchCondition.getFrozenFlag());
				
				StringBuffer sb=new StringBuffer();
				sb.append("select t.orderno,t.merchantno,t.merchantname,t.settledate,t.totalamt,t.totalnum,t.merchantfee,t.merchantsettleamt,t.settleflag,t.freezeflag,t.batchno,t.bankdeposit,t.accountname,t.accountno ");
				sb.append(" from t_transjnls_settle t where 1=1 ");
				
				if(!"".equals(mid)){
					sb.append(" and t.MERCHANTNO='").append(mid).append("' ");
				}
				if(!"".equals(realName)){
					sb.append(" and t.MERCHANTNAME like'%").append(realName).append("%' ");
				}
				if(!"".equals(settFlag)){
					sb.append(" and t.SETTLEFLAG='").append(settFlag).append("' ");
				}
				if(!"".equals(frozenFlag)){
					sb.append(" and t.FREEZEFLAG='").append(frozenFlag).append("' ");
				}
				
				sb.append(" and t.SETTLEDATE>='").append(startDate);
				sb.append("' and t.SETTLEDATE<='").append(endDate).append("'");
				
				List<Object> objList=sqlBaseDao.queryAll(sb.toString());
				if(objList==null || objList.size()<1){
					throw new ServiceException("根据条件查询无结果，导出结算Excel明细失败！");
				}
				HSSFSheet sheet=wb.createSheet("结算明细");
				HSSFCellStyle top_style =ExcelUtil.initCellStyle(wb, 16, false, false); // 首行标题样式对象 
				HSSFCellStyle data_style =ExcelUtil.initCellStyle(wb, 13, false, true); // 数据标题样式对象 
				HSSFCellStyle general_style =ExcelUtil.initCellStyle(wb, 0, true, false); // 普通样式对象 
				HSSFRow row=sheet.createRow(0);
				HSSFFont font = wb.createFont();
				HSSFCell cell=row.createCell((short)0);
				Region region = new Region((short)0,(short)0,(short)0,(short)12);      
				sheet.addMergedRegion(region);
				cell.setCellValue("结算明细"); // 表格的第一行第一列显示的数据   		
				cell.setCellStyle(top_style); // 样式，居中
	
				String[] coloumValue3={"商户号","商户名称","结算日期","总金额","总笔数","商户手续费","结算金额","结算状态","冻结状态","结算批次","结算银行","账号名称","结算账号"};
				row=ExcelUtil.createRow(sheet, 1, coloumValue3, data_style, true);
				Double amt=0.0;
				for(int i=0;i<objList.size();i++){
					Object[] objs=(Object[])objList.get(i);
					String[] col_i={
							        Constance.Null2EmptyTrim(objs[1]),
							        Constance.Null2EmptyTrim(objs[2]),
							        Constance.Null2EmptyTrim(objs[3]),
							        Constance.null2Amt(objs[4]),
							        Constance.null2Amt(objs[5]),
							        Constance.null2Amt(objs[6]),
							        Constance.null2Amt(objs[7]),
							        changeShowName(Constance.Null2EmptyTrim(objs[8]),"1"),
							        changeShowName(Constance.Null2EmptyTrim(objs[9]),"2"),
							        Constance.Null2EmptyTrim(objs[10]),
							        Constance.Null2EmptyTrim(objs[11]),
							        Constance.Null2EmptyTrim(objs[12]),
							        Constance.Null2EmptyTrim(objs[13])};
					row=ExcelUtil.createRow(sheet, 2+i, col_i, general_style, true);
					//amt=amt+Double.parseDouble( Constance.isMoney(objs[2].toString().trim()));
				}
				
			} catch (Exception e) {
				logger.error("########导出清算Excel明细失败######",e);
				throw new ServiceException("导出清算Excel明细失败！");
			}
		return wb;
	}
	/**
	 * 转换状态说明
	 * @param value
	 * @param 
	 * @return
	 */
	public String ShowName(String value,String fee){
		String ShowName = "";
		ShowName = value+" 手续费"+fee.replace(".", "。");
		return ShowName;
	}
	public String fuyan(String value,String fee,String mernoname){
		Date cDate = new Date();

		String cStr = DateFormatUtils.format(cDate, "yyyyMMdd");
		String ShowName = "";
		ShowName = cStr+"结算款YJ";
		return ShowName;
	}
	
	
	
	/**
	 * 转换状态说明
	 * @param value
	 * @param 
	 * @return
	 */
	public String ShowName1(String value){
		String ShowName = "";
		if(value.contains("中国银行")){
			ShowName="1";
		}else{
			ShowName="0";
		}
		return ShowName;
	}
	/**
	 * 转换状态说明
	 * @param value
	 * @param flag 1结算状态  2冻结状态
	 * @return
	 */
	public String changeShowName(String value,String flag){
		String ShowName = "";
		
		if("1".equals(flag)){
			if("Y".equals(value)){
				ShowName = "待审核";
			}else if("W".equals(value)){
				ShowName = "待出款";
			}else if("D".equals(value)){
				ShowName = "出款成功";
			}else if("S".equals(value)){
				ShowName = "出款失败";
			}
		}else if("2".equals(flag)){
			if("N".equals(value)){
				ShowName = "未冻结";
			}else if("Y".equals(value)){
				ShowName = "已冻结";
			}
		}
		
		
		return ShowName;
	}

	@Override
	public HSSFWorkbook hrtsettingExcel(
			HRTSettingSearchCondition searchCondition) throws ServiceException {

		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			
			String mid = Constance.Null2EmptyTrim(searchCondition.getMerNo());
			String startDate = searchCondition.getMinDate();
			String endDate = searchCondition.getMaxDate();
			String realName = Constance.Null2EmptyTrim(searchCondition.getRealName());
			String modelFlag = Constance.Null2EmptyTrim(searchCondition.getModelFlag());
			//String frozenFlag = Constance.Null2EmptyTrim(searchCondition.getFrozenFlag());
			
			StringBuffer sb=new StringBuffer();
			sb.append("select t.merchantsettleamt,'328561927099','北京和融通支付科技有限公司客户备付金',t.bankdeposit,t.deposino,t.accountno,t.accountname,t.settledate,t.merchantfee,t.merchantno,t.id,t.totalamt ");
			sb.append(" from t_transjnls_settleload t where 1=1 and t.freezeflag='N' ");
			
			if(!"".equals(mid)){
				sb.append(" and t.MERCHANTNO='").append(mid).append("' ");
			}
			if(!"".equals(realName)){
				sb.append(" and t.MERCHANTNAME like '%").append(realName).append("%' ");
			}
//			if(!"".equals(settFlag)){
//				sb.append(" and t.SETTLEFLAG='").append(settFlag).append("' ");
//			}
//			if(!"".equals(frozenFlag)){
//				sb.append(" and t.FREEZEFLAG='").append(frozenFlag).append("' ");
//			}
			if(!"".equals(modelFlag)){
				if("g".equals(modelFlag)){
					sb.append(" and t.acc_type='01' ");	
				}else if("c".equals(modelFlag)){
					sb.append(" and t.acc_type <> '01' and t.bankdeposit not like '中国银行%' and  t.merchantsettleamt<50000 and t.accountname not like '%·%' ");		
				}else if("k".equals(modelFlag)){
					sb.append(" and t.acc_type <> '01' and (t.bankdeposit  like '中国银行%' or  t.merchantsettleamt>50000) and t.accountname not like '%·%' ");	
				}else if("d".equals(modelFlag)){
					sb.append(" and t.acc_type <> '01' and t.accountname like '%·%' ");	
				}
				
				
			}
			sb.append(" and t.SETTLEDATE>='").append(startDate);
			sb.append("' and t.SETTLEDATE<='").append(endDate).append("'");
			
			List<Object> objList=sqlBaseDao.queryAll(sb.toString());
//			if(objList==null || objList.size()<1){
//				throw new ServiceException("根据条件查询无结果，导出结算Excel明细失败！");
//			}
			HSSFSheet sheet=wb.createSheet("结算明细");
			//HSSFCellStyle top_style =ExcelUtil.initCellStyle(wb, 16, false, false); // 首行标题样式对象 
			HSSFCellStyle data_style =ExcelUtil.initCellStyle(wb, 13, true, false); // 数据标题样式对象 
			HSSFCellStyle general_style =ExcelUtil.initCellStyle(wb, 0, true, false); // 普通样式对象 
			HSSFRow row=sheet.createRow(0);
			HSSFFont font = wb.createFont();
			//HSSFCell cell=row.createCell((short)0);
			//Region region = new Region((short)0,(short)0,(short)0,(short)12);      
			//sheet.addMergedRegion(region);
			//cell.setCellValue("结算明细"); // 表格的第一行第一列显示的数据   		
			//cell.setCellStyle(top_style); // 样式，居中

			String[] coloumValue3={"金额","付款人账号","付款人名称","收款人开户行名称","开户行号","开户账号","收款人名称","汇款用途","是否中行账号","是否同城","大商户号","序号","交易金额"};
			row=ExcelUtil.createRow(sheet, 0, coloumValue3, data_style, true);
			Double amt=0.0;
			for(int i=0;i<objList.size();i++){
				Object[] objs=(Object[])objList.get(i);
				String[] col_i={
						        Constance.null2Amt(objs[0]),
						        Constance.Null2EmptyTrim(objs[1]),
						        Constance.Null2EmptyTrim(objs[2]),
						        Constance.Null2EmptyTrim(objs[3]),
						        changeBankno(Constance.Null2EmptyTrim(objs[3]),Constance.Null2EmptyTrim(objs[4])),
						        Constance.null2Amt(objs[5]),
						        Constance.null2Amt(objs[6]),
						        ShowName(Constance.Null2EmptyTrim(objs[7]),Constance.Null2EmptyTrim(objs[8])),
						        ShowName1(Constance.Null2EmptyTrim(objs[3])),
						        "0",
						        Constance.Null2EmptyTrim(objs[9]),
						        Constance.Null2EmptyTrim(objs[10]),
						        Constance.null2Amt(objs[11])};
				row=ExcelUtil.createRow1(sheet, 1+i, col_i, general_style, false);
				//amt=amt+Double.parseDouble( Constance.isMoney(objs[2].toString().trim()));
			}
			
		} catch (Exception e) {
			logger.error("########导出清算Excel明细失败######",e);
			throw new ServiceException("导出清算Excel明细失败！");
		}
	return wb;
	}
	
	@Override
	public HSSFWorkbook settingExcel(String startDate,String endDate,String modelFlag,String excelflag) throws ServiceException {

		HSSFWorkbook wb = new HSSFWorkbook();
		try {
			
//			String mid = Constance.Null2EmptyTrim(searchCondition.getMerNo());
//			String startDate = searchCondition.getMinDate();
//			String endDate = searchCondition.getMaxDate();
//			String realName = Constance.Null2EmptyTrim(searchCondition.getRealName());
//			String modelFlag = Constance.Null2EmptyTrim(searchCondition.getModelFlag());
//			//String frozenFlag = Constance.Null2EmptyTrim(searchCondition.getFrozenFlag());
//			
			if("q".equals(modelFlag)||"w".equals(modelFlag)){
				StringBuffer sb=new StringBuffer();
				sb.append("select '328561927099','992013',t.merchantsettleamt,t.accountno,t.accountname,t.settledate,t.merchantfee,t.merchantname,t.bankdeposit,t.deposino,t.id,t.acc_type,t.totalamt,t.merchantno ");
				sb.append(" from t_transjnls_settle t where 1=1 and t.loadflag='0' and t.freezeflag='N' ");
				
				if(!"".equals(excelflag)){
					if("1".equals(excelflag)){
						sb.append(" and t.HOSTBRANTHNO='00000008' ");	
					}else if("2".equals(excelflag)){
						sb.append(" and t.HOSTBRANTHNO='00500380' ");	
					}
				}
				if(!"".equals(modelFlag)){
					if("q".equals(modelFlag)){
						sb.append("   and  t.merchantno not in(select ts.merno from t_settle_merno ts) ");	
//					}else if("c".equals(modelFlag)){
//						sb.append(" and t.acc_type <> '01' and t.bankdeposit not like '中国银行%' and  t.merchantsettleamt<50000 and t.accountname not like '%·%'  and  t.merchantno not in(select ts.merno from t_settle_merno ts) ");		
//					}else if("k".equals(modelFlag)){
//						sb.append(" and t.acc_type <> '01' and (t.bankdeposit  like '中国银行%' or  t.merchantsettleamt>=50000) and t.accountname not like '%·%'  and  t.merchantno not in(select ts.merno from t_settle_merno ts) ");	
//					}else if("d".equals(modelFlag)){
//						sb.append(" and t.acc_type <> '01' and t.accountname like '%·%'  and  t.merchantno not in(select ts.merno from t_settle_merno ts) ");	
					}else if("w".equals(modelFlag)){
						sb.append("  and  t.merchantno  in(select ts.merno from t_settle_merno ts) ");	
					}
				}
				sb.append(" and t.SETTLEDATE>='").append(startDate);
				sb.append("' and t.SETTLEDATE<='").append(endDate).append("'");
				
				List<Object> objList=sqlBaseDao.queryAll(sb.toString());
				System.out.println(sb.toString());
//				if(objList==null || objList.size()<1){
//					throw new ServiceException("根据条件查询无结果，导出结算Excel明细失败！");
//				}
				HSSFSheet sheet=wb.createSheet("结算明细");
				//HSSFCellStyle top_style =ExcelUtil.initCellStyle(wb, 16, false, false); // 首行标题样式对象 
				HSSFCellStyle data_style =ExcelUtil.initCellStyle(wb, 11, true, false); // 数据标题样式对象 
				HSSFCellStyle general_style =ExcelUtil.initCellStyle(wb, 0, true, false); // 普通样式对象 
				HSSFRow row=sheet.createRow(0);
				HSSFFont font = wb.createFont();
				//HSSFCell cell=row.createCell((short)0);
				//Region region = new Region((short)0,(short)0,(short)0,(short)12);      
				//sheet.addMergedRegion(region);
				//cell.setCellValue("结算明细"); // 表格的第一行第一列显示的数据   		
				//cell.setCellStyle(top_style); // 样式，居中

				String[] coloumValue3={"付款账号","商户号","付款金额","收款人账号","收款人名称","附言","收款行人行号","序列号","是否对公账户","交易金额","商户号"};
				row=ExcelUtil.createRow(sheet, 0, coloumValue3, data_style, true);
				Double amt=0.0;
				for(int i=0;i<objList.size();i++){
					Object[] objs=(Object[])objList.get(i);
					String[] col_i={
									Constance.Null2EmptyTrim(objs[0]),
									Constance.Null2EmptyTrim(objs[1]),
							        Constance.null2Amt(objs[2]),
							        Constance.Null2EmptyTrim(objs[3]),
							        Constance.Null2EmptyTrim(objs[4]),
							        fuyan(Constance.Null2EmptyTrim(objs[5]),Constance.Null2EmptyTrim(objs[6]),Constance.Null2EmptyTrim(objs[7])),
							        checkBankno(Constance.Null2EmptyTrim(objs[11]),Constance.Null2EmptyTrim(objs[8]),Constance.Null2EmptyTrim(objs[9]),Constance.null2Amt(objs[2])),
							        Constance.Null2EmptyTrim(objs[10]),
							        getacctype(Constance.Null2EmptyTrim(objs[11])),
							        Constance.Null2EmptyTrim(objs[12]),
							        Constance.Null2EmptyTrim(objs[13])};
					row=ExcelUtil.createRow1(sheet, 1+i, col_i, general_style, false);
					//amt=amt+Double.parseDouble( Constance.isMoney(objs[2].toString().trim()));
				}
			}else{
				
			
			
			
			StringBuffer sb=new StringBuffer();
			sb.append("select t.merchantsettleamt,'328561927099','北京和融通支付科技有限公司客户备付金',t.bankdeposit,t.deposino,t.accountno,t.accountname,t.settledate,t.merchantfee,t.merchantno,t.id,t.totalamt ");
			sb.append(" from t_transjnls_settle t where 1=1 and t.loadflag='0' and t.freezeflag='N' ");
			
//			if(!"".equals(mid)){
//				sb.append(" and t.MERCHANTNO='").append(mid).append("' ");
//			}
//			if(!"".equals(realName)){
//				sb.append(" and t.MERCHANTNAME like '%").append(realName).append("%' ");
//			}
//			if(!"".equals(settFlag)){
//				sb.append(" and t.SETTLEFLAG='").append(settFlag).append("' ");
//			}
//			if(!"".equals(frozenFlag)){
//				sb.append(" and t.FREEZEFLAG='").append(frozenFlag).append("' ");
//			}
			
			if(!"".equals(excelflag)){
				if("1".equals(excelflag)){
					sb.append(" and t.HOSTBRANTHNO='00000008' ");	
				}else if("2".equals(excelflag)){
					sb.append(" and t.HOSTBRANTHNO='00500380' ");	
				}
			}
			if(!"".equals(modelFlag)){
				if("g".equals(modelFlag)){
					sb.append(" and t.acc_type='01'  and  t.merchantno not in(select ts.merno from t_settle_merno ts) ");	
				}else if("c".equals(modelFlag)){
					sb.append(" and t.acc_type <> '01' and t.bankdeposit not like '中国银行%' and  t.merchantsettleamt<50000 and t.accountname not like '%·%'  and  t.merchantno not in(select ts.merno from t_settle_merno ts) ");		
				}else if("k".equals(modelFlag)){
					sb.append(" and t.acc_type <> '01' and (t.bankdeposit  like '中国银行%' or  t.merchantsettleamt>=50000) and t.accountname not like '%·%'  and  t.merchantno not in(select ts.merno from t_settle_merno ts) ");	
				}else if("d".equals(modelFlag)){
					sb.append(" and t.acc_type <> '01' and t.accountname like '%·%'  and  t.merchantno not in(select ts.merno from t_settle_merno ts) ");	
				}else if("w".equals(modelFlag)){
					sb.append("  and  t.merchantno  in(select ts.merno from t_settle_merno ts) ");	
				}
			}
			sb.append(" and t.SETTLEDATE>='").append(startDate);
			sb.append("' and t.SETTLEDATE<='").append(endDate).append("'");
			
			List<Object> objList=sqlBaseDao.queryAll(sb.toString());
			System.out.println(sb.toString());
//			if(objList==null || objList.size()<1){
//				throw new ServiceException("根据条件查询无结果，导出结算Excel明细失败！");
//			}
			HSSFSheet sheet=wb.createSheet("结算明细");
			//HSSFCellStyle top_style =ExcelUtil.initCellStyle(wb, 16, false, false); // 首行标题样式对象 
			HSSFCellStyle data_style =ExcelUtil.initCellStyle(wb, 13, true, false); // 数据标题样式对象 
			HSSFCellStyle general_style =ExcelUtil.initCellStyle(wb, 0, true, false); // 普通样式对象 
			HSSFRow row=sheet.createRow(0);
			HSSFFont font = wb.createFont();
			//HSSFCell cell=row.createCell((short)0);
			//Region region = new Region((short)0,(short)0,(short)0,(short)12);      
			//sheet.addMergedRegion(region);
			//cell.setCellValue("结算明细"); // 表格的第一行第一列显示的数据   		
			//cell.setCellStyle(top_style); // 样式，居中

			String[] coloumValue3={"金额","付款人账号","付款人名称","收款人开户行名称","开户行号","开户账号","收款人名称","汇款用途","是否中行账号","是否同城","大商户号","序号","交易金额"};
			row=ExcelUtil.createRow(sheet, 0, coloumValue3, data_style, true);
			Double amt=0.0;
			for(int i=0;i<objList.size();i++){
				Object[] objs=(Object[])objList.get(i);
				String[] col_i={
						        Constance.null2Amt(objs[0]),
						        Constance.Null2EmptyTrim(objs[1]),
						        Constance.Null2EmptyTrim(objs[2]),
						        Constance.Null2EmptyTrim(objs[3]),
						        changeBankno(Constance.Null2EmptyTrim(objs[3]),Constance.Null2EmptyTrim(objs[4])),
						        Constance.null2Amt(objs[5]),
						        Constance.null2Amt(objs[6]),
						        ShowName(Constance.Null2EmptyTrim(objs[7]),Constance.Null2EmptyTrim(objs[8])),
						        ShowName1(Constance.Null2EmptyTrim(objs[3])),
						        "0",
						        Constance.Null2EmptyTrim(objs[9]),
						        Constance.Null2EmptyTrim(objs[10]),
						        Constance.null2Amt(objs[11])};
				row=ExcelUtil.createRow1(sheet, 1+i, col_i, general_style, false);
				//amt=amt+Double.parseDouble( Constance.isMoney(objs[2].toString().trim()));
			}
			}
		} catch (Exception e) {
			logger.error("########导出清算Excel明细失败######",e);
			throw new ServiceException("导出清算Excel明细失败！");
		}
	return wb;
	}
	
	/**
	 * 转换公私。
	 * @param value
	 * @param 
	 * @return
	 */
	public String getacctype(String accvalue){
		String ShowName = "";
		if(accvalue.equals("01")){
			ShowName="1";
		}else {
			ShowName="0";
		}
		return ShowName;
	}
	/**
	 * 转换银行号
	 * @param value
	 * @param 
	 * @return
	 */
	public String checkBankno(String accvalue,String bankname,String bankno,String settingamt){
		String ShowName = "";
		if(accvalue.equals("01")){
			if(bankname.contains("中国银行")){
			ShowName="";
			}else{
				ShowName=bankno;
			}
		}else {
			if(bankname.contains("中国银行")){
				ShowName="11";
			}else if(Double.valueOf(settingamt)<50000){
				try {
					ShowName = getBankno(bankname);
				} catch (DaoException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}else{
				ShowName=bankno;
			}
		}
		return ShowName;
	}
	
	/**
	 * 转换状态说明
	 * @param value
	 * @param flag 1结算状态  2冻结状态
	 * @return
	 */
	public String changeBankno(String value,String bankno){
		String ShowName = "";
		
		if(value.contains("中国银行")){
			ShowName="11";
		}else{
			try {
				ShowName =getBankno(value);
			} catch (DaoException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			if(ShowName==null||"".equals(ShowName)){
				ShowName=bankno;
			}
		}
		return ShowName;
	}
	/**
	 * 根据开户行名称查询联行号
	 * @param value
	 * @return
	 * @throws DaoException 
	 */
	public String getBankno(String value) throws DaoException{
		String str ="";
		
		String bankname = value.substring(0, 6);
				String sql=" select t.branchid from t_bank_info t where t.bankname like '"+bankname+"%'";
				List<Object> objList=sqlBaseDao.queryAll(sql);
				for(int i=0;i<objList.size();i++){
					str = (String) objList.get(0);
				}
		return str;
	}
}
